package com.snackshop;

import com.github.javafaker.Faker;
import picocli.CommandLine;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.concurrent.Callable;
import java.util.concurrent.TimeUnit;

@Command(name = "snack-mock-generator", mixinStandardHelpOptions = true, version = "1.0",
        description = "Generates mock data for snack e-commerce database")
public class MockGenerator implements Callable<Integer> {

    // 命令行参数
    @Option(names = {"-u", "--users"}, description = "Number of users to generate")
    private int userCount = 100;

    @Option(names = {"-p", "--products"}, description = "Number of products to generate")
    private int productCount = 50;

    @Option(names = {"-o", "--orders"}, description = "Number of orders to generate")
    private int orderCount = 500;

    @Option(names = {"-c", "--categories"}, description = "Number of categories to generate")
    private int categoryCount = 15;

    @Option(names = {"-d", "--db-url"}, description = "Database URL")
    private String dbUrl = "jdbc:mysql://localhost:3306/snack_shop";

    @Option(names = {"-n", "--db-user"}, description = "Database username")
    private String dbUser = "root";

    @Option(names = {"-w", "--db-password"}, description = "Database password")
    private String dbPassword = "password";

    @Option(names = {"-f", "--output-file"}, description = "Output SQL file")
    private String outputFile;

    @Option(names = {"-s", "--skip-db"}, description = "Skip database insertion")
    private boolean skipDbInsertion;

    // 数据生成工具
    private Faker faker = new Faker(new Locale("zh-CN"));
    private Connection connection;
    private StringBuilder sqlBuilder = new StringBuilder();

    // 预定义数据
    private static final List<String> SNACK_BRANDS = Arrays.asList(
            "三只松鼠", "良品铺子", "百草味", "来伊份", "洽洽", "旺旺",
            "徐福记", "德芙", "奥利奥", "乐事", "好丽友", "格力高");

    private static final List<String> SNACK_TYPES = Arrays.asList(
            "坚果", "薯片", "巧克力", "饼干", "糖果", "肉干",
            "果冻", "海苔", "糕点", "膨化食品", "豆制品", "蜜饯");

    private static final List<String> FLAVORS = Arrays.asList(
            "原味", "麻辣", "烧烤", "番茄", "海盐", "芝士",
            "蜂蜜", "奶油", "香辣", "五香", "芥末", "草莓");

    private static final List<String> DELIVERY_COMPANIES = Arrays.asList(
            "顺丰", "中通", "圆通", "韵达", "申通", "京东物流", "EMS");

    public static void main(String[] args) {
        int exitCode = new CommandLine(new MockGenerator()).execute(args);
        System.exit(exitCode);
    }

    @Override
    public Integer call() throws Exception {
        System.out.println("Starting mock data generation...");
        System.out.printf("Generating: %d users, %d products, %d orders, %d categories%n",
                userCount, productCount, orderCount, categoryCount);

        if (!skipDbInsertion) {
            connectToDatabase();
        }

        // 生成顺序很重要，保持数据完整性
        List<Integer> categoryIds = generateCategories();
        List<Integer> userIds = generateUsers();
        Map<Integer, List<Integer>> userAddressMap = generateUserAddresses(userIds);
        List<Product> products = generateProducts(categoryIds);
        Map<Integer, List<ProductSku>> productSkusMap = generateProductSkus(products);
        List<Coupon> coupons = generateCoupons();
        Map<Integer, List<UserCoupon>> userCouponsMap = generateUserCoupons(userIds, coupons);
        generateCarts(userIds, productSkusMap);
        generateOrders(userIds, userAddressMap, productSkusMap, userCouponsMap);
        generateInventoryOperations(productSkusMap);

        if (outputFile != null) {
            saveToFile();
        }

        if (!skipDbInsertion) {
            System.out.println("Inserting data into database...");
            executeSqlStatements();
            connection.close();
        }

        System.out.println("Mock data generation completed successfully!");
        return 0;
    }

    // === 数据生成方法 ===

    private List<Integer> generateCategories() {
        System.out.println("Generating categories...");
        List<Integer> categoryIds = new ArrayList<>();
        String sql = "INSERT INTO product_categories (parent_id, name, level, sort_order, is_visible) VALUES (?, ?, ?, ?, ?)";

        // 生成顶级分类
        int topLevelCount = Math.max(3, categoryCount / 3);
        for (int i = 0; i < topLevelCount; i++) {
            String name = SNACK_TYPES.get(i % SNACK_TYPES.size()) + "零食";
            if (skipDbInsertion) {
                sqlBuilder.append(String.format(
                        "INSERT INTO product_categories (parent_id, name, level, sort_order, is_visible) " +
                                "VALUES (NULL, '%s', 1, %d, 1);\n", name, i + 1));
            } else {
                try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                    pstmt.setNull(1, Types.INTEGER);
                    pstmt.setString(2, name);
                    pstmt.setInt(3, 1);
                    pstmt.setInt(4, i + 1);
                    pstmt.setInt(5, 1);
                    pstmt.executeUpdate();

                    try (ResultSet rs = pstmt.getGeneratedKeys()) {
                        if (rs.next()) {
                            categoryIds.add(rs.getInt(1));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        // 生成二级分类
        for (int parentId : categoryIds) {
            int subCount = faker.random().nextInt(1, 3);
            for (int j = 0; j < subCount; j++) {
                String name = FLAVORS.get(j % FLAVORS.size()) + SNACK_TYPES.get(j % SNACK_TYPES.size());
                if (skipDbInsertion) {
                    sqlBuilder.append(String.format(
                            "INSERT INTO product_categories (parent_id, name, level, sort_order, is_visible) " +
                                    "VALUES (%d, '%s', 2, %d, 1);\n", parentId, name, j + 1));
                } else {
                    try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                        pstmt.setInt(1, parentId);
                        pstmt.setString(2, name);
                        pstmt.setInt(3, 2);
                        pstmt.setInt(4, j + 1);
                        pstmt.setInt(5, 1);
                        pstmt.executeUpdate();

                        try (ResultSet rs = pstmt.getGeneratedKeys()) {
                            if (rs.next()) {
                                categoryIds.add(rs.getInt(1));
                            }
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return categoryIds;
    }

    private List<Integer> generateUsers() {
        System.out.println("Generating users...");
        List<Integer> userIds = new ArrayList<>();
        String sql = "INSERT INTO users (username, password_hash, nickname, mobile, email, avatar, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        for (int i = 0; i < userCount; i++) {
            String username = "user" + faker.number().numberBetween(10000, 99999);
            String password = faker.internet().password();
            String nickname = faker.name().fullName();
            String mobile = "1" + faker.number().numberBetween(300000000, 399999999);
            String email = faker.internet().emailAddress();
            String avatar = "https://avatar.com/" + faker.internet().uuid() + ".jpg";
            int status = 1; // 激活状态

            if (skipDbInsertion) {
                sqlBuilder.append(String.format(
                        "INSERT INTO users (username, password_hash, nickname, mobile, email, avatar, status) " +
                                "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', %d);\n",
                        username, password, nickname, mobile, email, avatar, status));
            } else {
                try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                    pstmt.setString(1, username);
                    pstmt.setString(2, password);
                    pstmt.setString(3, nickname);
                    pstmt.setString(4, mobile);
                    pstmt.setString(5, email);
                    pstmt.setString(6, avatar);
                    pstmt.setInt(7, status);
                    pstmt.executeUpdate();

                    try (ResultSet rs = pstmt.getGeneratedKeys()) {
                        if (rs.next()) {
                            userIds.add(rs.getInt(1));
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return userIds;
    }

    private Map<Integer, List<Integer>> generateUserAddresses(List<Integer> userIds) {
        System.out.println("Generating user addresses...");
        Map<Integer, List<Integer>> userAddressMap = new HashMap<>();
        String sql = "INSERT INTO user_addresses (user_id, consignee, mobile, province, city, district, detail, postal_code, is_default) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        for (int userId : userIds) {
            List<Integer> addressIds = new ArrayList<>();
            int addressCount = faker.random().nextInt(1, 3); // 每个用户1-3个地址

            for (int i = 0; i < addressCount; i++) {
                String consignee = faker.name().fullName();
                String mobile = faker.phoneNumber().cellPhone();
                String province = faker.address().state();
                String city = faker.address().city();
                String district = faker.address().streetName();
                String detail = faker.address().streetAddress();
                String postalCode = faker.address().zipCode();
                int isDefault = (i == 0) ? 1 : 0; // 第一个地址设为默认地址

                if (skipDbInsertion) {
                    sqlBuilder.append(String.format(
                            "INSERT INTO user_addresses (user_id, consignee, mobile, province, city, district, detail, postal_code, is_default) " +
                                    "VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d);\n",
                            userId, consignee, mobile, province, city, district, detail, postalCode, isDefault));
                } else {
                    try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                        pstmt.setInt(1, userId);
                        pstmt.setString(2, consignee);
                        pstmt.setString(3, mobile);
                        pstmt.setString(4, province);
                        pstmt.setString(5, city);
                        pstmt.setString(6, district);
                        pstmt.setString(7, detail);
                        pstmt.setString(8, postalCode);
                        pstmt.setInt(9, isDefault);
                        pstmt.executeUpdate();

                        try (ResultSet rs = pstmt.getGeneratedKeys()) {
                            if (rs.next()) {
                                addressIds.add(rs.getInt(1));
                            }
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            userAddressMap.put(userId, addressIds);
        }
        return userAddressMap;
    }

    private List<Product> generateProducts(List<Integer> categoryIds) {
        System.out.println("Generating products...");
        List<Product> products = new ArrayList<>();
        String sql = "INSERT INTO products (category_id, name, subtitle, main_image, origin_price, status, brand, unit, weight) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        for (int i = 0; i < productCount; i++) {
            Product product = new Product();
            product.categoryId = categoryIds.get(faker.random().nextInt(categoryIds.size()));
            product.name = SNACK_BRANDS.get(faker.random().nextInt(SNACK_BRANDS.size())) + " " +
                    faker.food().ingredient() + " " +
                    SNACK_TYPES.get(faker.random().nextInt(SNACK_TYPES.size()));
            product.subtitle = faker.lorem().sentence(3);
            product.mainImage = "https://product-images.com/" + faker.internet().uuid() + ".jpg";
            product.originPrice = faker.number().randomDouble(2, 5, 100);
            product.status = 1; // 上架
            product.brand = SNACK_BRANDS.get(faker.random().nextInt(SNACK_BRANDS.size()));
            product.unit = faker.options().option("袋", "盒", "罐", "包", "箱");
            product.weight = faker.number().randomDouble(3, (int) 0.1, (int) 1.0);

            if (skipDbInsertion) {
                sqlBuilder.append(String.format(
                        "INSERT INTO products (category_id, name, subtitle, main_image, origin_price, status, brand, unit, weight) " +
                                "VALUES (%d, '%s', '%s', '%s', %.2f, %d, '%s', '%s', %.3f);\n",
                        product.categoryId, product.name, product.subtitle, product.mainImage,
                        product.originPrice, product.status, product.brand, product.unit, product.weight
                ));
            } else {
                try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                    pstmt.setInt(1, product.categoryId);
                    pstmt.setString(2, product.name);
                    pstmt.setString(3, product.subtitle);
                    pstmt.setString(4, product.mainImage);
                    pstmt.setDouble(5, product.originPrice);
                    pstmt.setInt(6, product.status);
                    pstmt.setString(7, product.brand);
                    pstmt.setString(8, product.unit);
                    pstmt.setDouble(9, product.weight);
                    pstmt.executeUpdate();

                    try (ResultSet rs = pstmt.getGeneratedKeys()) {
                        if (rs.next()) {
                            product.id = rs.getInt(1);
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            products.add(product);
        }
        return products;
    }

    private Map<Integer, List<ProductSku>> generateProductSkus(List<Product> products) {
        System.out.println("Generating product SKUs...");
        Map<Integer, List<ProductSku>> skusMap = new HashMap<>();
        String sql = "INSERT INTO product_skus (product_id, name, attributes, price, stock, sales, sku_code, image) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        for (Product product : products) {
            List<ProductSku> skus = new ArrayList<>();
            int skuCount = faker.random().nextInt(1, 3); // 每个商品1-3个SKU

            for (int i = 0; i < skuCount; i++) {
                ProductSku sku = new ProductSku();
                sku.productId = product.id;
                sku.product = product;

                // 生成SKU名称和属性
                String weightUnit = faker.options().option("g", "kg", "袋", "盒", "罐");
                int weightValue = faker.random().nextInt(50, 1000);
                String flavor = FLAVORS.get(faker.random().nextInt(FLAVORS.size()));
                sku.name = weightValue + weightUnit + "/" + flavor;
                sku.attributes = "{\"规格\":\"" + weightValue + weightUnit + "\", \"口味\":\"" + flavor + "\"}";

                // 价格在商品基础价上浮动
                sku.price = product.originPrice * (0.8 + 0.4 * faker.random().nextDouble());
                sku.stock = faker.random().nextInt(50, 500);
                sku.sales = faker.random().nextInt(0, sku.stock/2);
                sku.skuCode = "SKU" + faker.number().numberBetween(100000, 999999);
                sku.image = product.mainImage; // 默认使用商品主图

                if (skipDbInsertion) {
                    sqlBuilder.append(String.format(
                            "INSERT INTO product_skus (product_id, name, attributes, price, stock, sales, sku_code, image) " +
                                    "VALUES (%d, '%s', '%s', %.2f, %d, %d, '%s', '%s');\n",
                            product.id, sku.name, sku.attributes, sku.price, sku.stock, sku.sales, sku.skuCode, sku.image
                    ));
                } else {
                    try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                        pstmt.setInt(1, product.id);
                        pstmt.setString(2, sku.name);
                        pstmt.setString(3, sku.attributes);
                        pstmt.setDouble(4, sku.price);
                        pstmt.setInt(5, sku.stock);
                        pstmt.setInt(6, sku.sales);
                        pstmt.setString(7, sku.skuCode);
                        pstmt.setString(8, sku.image);
                        pstmt.executeUpdate();

                        try (ResultSet rs = pstmt.getGeneratedKeys()) {
                            if (rs.next()) {
                                sku.id = rs.getInt(1);
                            }
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                skus.add(sku);
            }
            skusMap.put(product.id, skus);
        }
        return skusMap;
    }

    private List<Coupon> generateCoupons() {
        System.out.println("Generating coupons...");
        List<Coupon> coupons = new ArrayList<>();
        String sql = "INSERT INTO coupons (name, code, type, discount, min_amount, amount, total_count, " +
                "used_count, validity_type, validity_start, validity_end, validity_days, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        // 优惠券类型: 1=满减, 2=折扣, 3=无门槛
        for (int i = 0; i < 15; i++) { // 固定生成15种优惠券
            String name = faker.commerce().promotionCode() + "优惠券";
            String code = "CPN" + faker.number().numberBetween(1000, 9999);
            int type = faker.random().nextInt(1, 3);
            Double discount = (type == 2) ? faker.number().randomDouble(2, 70, 95) / 100 : null;
            Double minAmount = (type == 1) ? faker.number().randomDouble(2, 50, 300) : 0.0;
            Double amount = (type != 2) ? faker.number().randomDouble(2, 5, 50) : null;

            int totalCount = faker.number().numberBetween(100, 1000);
            int usedCount = faker.number().numberBetween(0, totalCount/2);
            int validityType = faker.random().nextInt(1, 2);

            java.util.Date validityStart = null;
            java.util.Date validityEnd = null;
            Integer validityDays = null;

            if (validityType == 1) {
                validityStart = faker.date().past(30, TimeUnit.DAYS);
                validityEnd = faker.date().future(90, TimeUnit.DAYS);
            } else {
                validityDays = faker.number().numberBetween(7, 30);
            }

            int status = faker.random().nextInt(0, 1); // 0=无效, 1=有效

            if (skipDbInsertion) {
                sqlBuilder.append(String.format(
                        "INSERT INTO coupons (name, code, type, discount, min_amount, amount, total_count, " +
                                "used_count, validity_type, validity_start, validity_end, validity_days, status) " +
                                "VALUES ('%s', '%s', %d, %s, %s, %s, %d, %d, %d, %s, %s, %s, %d);\n",
                        name, code, type,
                        discount != null ? discount : "NULL",
                        minAmount != null ? minAmount : "NULL",
                        amount != null ? amount : "NULL",
                        totalCount, usedCount, validityType,
                        validityStart != null ? "'" + new java.sql.Date(validityStart.getTime()) + "'" : "NULL",
                        validityEnd != null ? "'" + new java.sql.Date(validityEnd.getTime()) + "'" : "NULL",
                        validityDays != null ? validityDays : "NULL",
                        status
                ));
            } else {
                try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                    pstmt.setString(1, name);
                    pstmt.setString(2, code);
                    pstmt.setInt(3, type);
                    setNullableDouble(pstmt, 4, discount);
                    setNullableDouble(pstmt, 5, minAmount);
                    setNullableDouble(pstmt, 6, amount);
                    pstmt.setInt(7, totalCount);
                    pstmt.setInt(8, usedCount);
                    pstmt.setInt(9, validityType);
                    setNullableDate(pstmt, 10, validityStart);
                    setNullableDate(pstmt, 11, validityEnd);
                    setNullableInt(pstmt, 12, validityDays);
                    pstmt.setInt(13, status);
                    pstmt.executeUpdate();

                    try (ResultSet rs = pstmt.getGeneratedKeys()) {
                        if (rs.next()) {
                            Coupon coupon = new Coupon();
                            coupon.id = rs.getInt(1);
                            coupons.add(coupon);
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return coupons;
    }

    private Map<Integer, List<UserCoupon>> generateUserCoupons(List<Integer> userIds, List<Coupon> coupons) {
        System.out.println("Generating user coupons...");
        Map<Integer, List<UserCoupon>> userCouponsMap = new HashMap<>();
        String sql = "INSERT INTO user_coupons (user_id, coupon_id, code, status, start_time, end_time) " +
                "VALUES (?, ?, ?, ?, ?, ?)";

        for (int userId : userIds) {
            List<UserCoupon> userCoupons = new ArrayList<>();
            int couponCount = faker.random().nextInt(0, 5); // 每个用户0-5张优惠券

            for (int i = 0; i < couponCount; i++) {
                Coupon coupon = coupons.get(faker.random().nextInt(coupons.size()));
                String code = "UC-" + faker.number().numberBetween(100000, 999999);
                int status = faker.random().nextInt(0, 2); // 0=未使用, 1=已使用, 2=已过期

                java.util.Date startTime = faker.date().past(10, TimeUnit.DAYS);
                java.util.Date endTime = faker.date().future(30, TimeUnit.DAYS);

                if (skipDbInsertion) {
                    sqlBuilder.append(String.format(
                            "INSERT INTO user_coupons (user_id, coupon_id, code, status, start_time, end_time) " +
                                    "VALUES (%d, %d, '%s', %d, '%s', '%s');\n",
                            userId, coupon.id, code, status,
                            new java.sql.Date(startTime.getTime()),
                            new java.sql.Date(endTime.getTime())
                    ));
                } else {
                    try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                        pstmt.setInt(1, userId);
                        pstmt.setInt(2, coupon.id);
                        pstmt.setString(3, code);
                        pstmt.setInt(4, status);
                        pstmt.setDate(5, new java.sql.Date(startTime.getTime()));
                        pstmt.setDate(6, new java.sql.Date(endTime.getTime()));
                        pstmt.executeUpdate();

                        try (ResultSet rs = pstmt.getGeneratedKeys()) {
                            if (rs.next()) {
                                UserCoupon userCoupon = new UserCoupon();
                                userCoupon.id = rs.getInt(1);
                                userCoupons.add(userCoupon);
                            }
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            userCouponsMap.put(userId, userCoupons);
        }
        return userCouponsMap;
    }

    private void generateCarts(List<Integer> userIds, Map<Integer, List<ProductSku>> productSkusMap) {
        System.out.println("Generating shopping carts...");
        String sql = "INSERT INTO carts (user_id, sku_id, quantity, selected) VALUES (?, ?, ?, ?)";

        for (int userId : userIds) {
            // 30%的用户有购物车
            if (faker.random().nextDouble() < 0.3) {
                int cartItemCount = faker.random().nextInt(1, 8); // 每个购物车1-8个商品

                for (int i = 0; i < cartItemCount; i++) {
                    // 随机选择一个SKU
                    int randomProductId = new ArrayList<>(productSkusMap.keySet())
                            .get(faker.random().nextInt(productSkusMap.size()));
                    ProductSku sku = productSkusMap.get(randomProductId)
                            .get(faker.random().nextInt(productSkusMap.get(randomProductId).size()));

                    int quantity = faker.random().nextInt(1, 5);
                    int selected = faker.random().nextInt(0, 1);

                    if (skipDbInsertion) {
                        sqlBuilder.append(String.format(
                                "INSERT INTO carts (user_id, sku_id, quantity, selected) " +
                                        "VALUES (%d, %d, %d, %d);\n",
                                userId, sku.id, quantity, selected
                        ));
                    } else {
                        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                            pstmt.setInt(1, userId);
                            pstmt.setInt(2, sku.id);
                            pstmt.setInt(3, quantity);
                            pstmt.setInt(4, selected);
                            pstmt.executeUpdate();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    }

    private void generateOrders(List<Integer> userIds,
                                Map<Integer, List<Integer>> userAddressMap,
                                Map<Integer, List<ProductSku>> productSkusMap,
                                Map<Integer, List<UserCoupon>> userCouponsMap) {
        System.out.println("Generating orders...");

        for (int i = 0; i < orderCount; i++) {
            // 选择随机用户
            int userId = userIds.get(faker.random().nextInt(userIds.size()));

            // 获取用户地址
            List<Integer> addresses = userAddressMap.get(userId);
            int addressId = addresses.get(faker.random().nextInt(addresses.size()));

            // 创建订单
            try {
                // 生成订单基本信息
                String orderNo = "ORDER" + faker.number().numberBetween(100000000, 999999999);
                String consignee = faker.name().fullName();
                String mobile = faker.phoneNumber().cellPhone();
                String fullAddress = faker.address().fullAddress();

                // 生成订单项
                int itemCount = faker.random().nextInt(1, 6);
                double totalAmount = 0;
                List<OrderItem> items = new ArrayList<>();

                for (int j = 0; j < itemCount; j++) {
                    // 随机选择SKU
                    int randomProductId = new ArrayList<>(productSkusMap.keySet())
                            .get(faker.random().nextInt(productSkusMap.size()));
                    ProductSku sku = productSkusMap.get(randomProductId)
                            .get(faker.random().nextInt(productSkusMap.get(randomProductId).size()));

                    int quantity = faker.random().nextInt(1, 3);
                    double price = sku.price;
                    double itemTotal = price * quantity;
                    totalAmount += itemTotal;

                    items.add(new OrderItem(
                            sku.productId,
                            sku.product.name,
                            sku.product.mainImage,
                            sku.id,
                            sku.name,
                            sku.attributes,
                            price,
                            quantity,
                            itemTotal
                    ));
                }

                // 计算运费和优惠
                double freightAmount = totalAmount > 100 ? 0 : faker.number().randomDouble(2, 5, 20);
                double promotionAmount = faker.number().randomDouble(2, 0, (int) (totalAmount*0.2));
                double couponAmount = 0;

                // 随机使用优惠券
                List<UserCoupon> userCoupons = userCouponsMap.getOrDefault(userId, new ArrayList<>());
                if (!userCoupons.isEmpty() && faker.random().nextDouble() < 0.4) {
                    UserCoupon coupon = userCoupons.get(faker.random().nextInt(userCoupons.size()));
                    if (coupon.status == 0) { // 仅使用未使用的优惠券
                        couponAmount = faker.number().randomDouble(2, 5, 30);
                    }
                }

                double payAmount = totalAmount + freightAmount - promotionAmount - couponAmount;

                // 设置订单状态
                int orderStatus = faker.random().nextInt(0, 5);
                int paymentMethod = faker.random().nextInt(1, 3); // 1=微信, 2=支付宝, 3=余额

                // 时间线逻辑
                java.util.Date createTime = faker.date().past(30, TimeUnit.DAYS);
                java.util.Date paymentTime = orderStatus > 0 ? faker.date().future(1, TimeUnit.MINUTES, createTime) : null;
                java.util.Date deliveryTime = orderStatus > 1 ? faker.date().future(1, TimeUnit.DAYS, paymentTime) : null;
                java.util.Date receiveTime = orderStatus > 2 ? faker.date().future(2, TimeUnit.DAYS, deliveryTime) : null;

                // 物流信息
                String deliveryCompany = orderStatus > 1 ? DELIVERY_COMPANIES.get(faker.random().nextInt(DELIVERY_COMPANIES.size())) : null;
                String deliveryNo = deliveryCompany != null ? faker.regexify("[A-Z]{2}\\d{10}") : null;

                // 订单备注
                String remark = faker.random().nextDouble() < 0.3 ? faker.lorem().sentence() : null;

                // 插入订单
                if (skipDbInsertion) {
                    // 生成订单SQL
                    sqlBuilder.append(String.format(
                            "INSERT INTO orders (user_id, address_id, consignee, mobile, full_address, " +
                                    "total_amount, freight_amount, promotion_amount, coupon_amount, pay_amount, " +
                                    "payment_method, payment_time, order_status, delivery_company, delivery_no, " +
                                    "delivery_time, receive_time, remark, order_no, create_time, update_time) " +
                                    "VALUES (%d, %d, '%s', '%s', '%s', %.2f, %.2f, %.2f, %.2f, %.2f, %d, %s, %d, %s, %s, %s, %s, %s, '%s', '%s', '%s');\n",
                            userId, addressId, consignee, mobile, fullAddress,
                            totalAmount, freightAmount, promotionAmount, couponAmount, payAmount,
                            paymentMethod,
                            paymentTime != null ? "'" + new Timestamp(paymentTime.getTime()) + "'" : "NULL",
                            orderStatus,
                            deliveryCompany != null ? "'" + deliveryCompany + "'" : "NULL",
                            deliveryNo != null ? "'" + deliveryNo + "'" : "NULL",
                            deliveryTime != null ? "'" + new Timestamp(deliveryTime.getTime()) + "'" : "NULL",
                            receiveTime != null ? "'" + new Timestamp(receiveTime.getTime()) + "'" : "NULL",
                            remark != null ? "'" + remark.replace("'", "''") + "'" : "NULL",
                            orderNo,
                            new Timestamp(createTime.getTime()),
                            new Timestamp(createTime.getTime())
                    ));

                    // 生成订单项SQL
                    for (OrderItem item : items) {
                        sqlBuilder.append(String.format(
                                "INSERT INTO order_items (order_id, order_no, product_id, product_name, " +
                                        "product_main_image, sku_id, sku_name, sku_attributes, price, quantity, total_price) " +
                                        "VALUES (LAST_INSERT_ID(), '%s', %d, '%s', '%s', %d, '%s', '%s', %.2f, %d, %.2f);\n",
                                orderNo, item.productId, item.productName, item.productMainImage,
                                item.skuId, item.skuName, item.skuAttributes, item.price, item.quantity, item.totalPrice
                        ));
                    }

                    // 生成支付记录
                    if (paymentTime != null) {
                        String transactionNo = "PAY" + faker.number().numberBetween(100000000, 999999999);
                        sqlBuilder.append(String.format(
                                "INSERT INTO payments (order_id, order_no, transaction_no, " +
                                        "payment_method, amount, status, pay_time) " +
                                        "VALUES (LAST_INSERT_ID(), '%s', '%s', %d, %.2f, %d, '%s');\n",
                                orderNo, transactionNo, paymentMethod, payAmount, 1, new Timestamp(paymentTime.getTime())
                        ));
                    }
                } else {
                    // 数据库插入逻辑
                    try {
                        connection.setAutoCommit(false);

                        // 插入订单
                        try (PreparedStatement orderStmt = connection.prepareStatement(
                                "INSERT INTO orders (user_id, address_id, consignee, mobile, full_address, " +
                                        "total_amount, freight_amount, promotion_amount, coupon_amount, pay_amount, " +
                                        "payment_method, payment_time, order_status, delivery_company, delivery_no, " +
                                        "delivery_time, receive_time, remark, order_no, create_time, update_time) " +
                                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                                Statement.RETURN_GENERATED_KEYS)) {

                            orderStmt.setInt(1, userId);
                            orderStmt.setInt(2, addressId);
                            orderStmt.setString(3, consignee);
                            orderStmt.setString(4, mobile);
                            orderStmt.setString(5, fullAddress);
                            orderStmt.setDouble(6, totalAmount);
                            orderStmt.setDouble(7, freightAmount);
                            orderStmt.setDouble(8, promotionAmount);
                            orderStmt.setDouble(9, couponAmount);
                            orderStmt.setDouble(10, payAmount);
                            orderStmt.setInt(11, paymentMethod);
                            setNullableTimestamp(orderStmt, 12, paymentTime);
                            orderStmt.setInt(13, orderStatus);
                            setNullableString(orderStmt, 14, deliveryCompany);
                            setNullableString(orderStmt, 15, deliveryNo);
                            setNullableTimestamp(orderStmt, 16, deliveryTime);
                            setNullableTimestamp(orderStmt, 17, receiveTime);
                            setNullableString(orderStmt, 18, remark);
                            orderStmt.setString(19, orderNo);
                            orderStmt.setTimestamp(20, new Timestamp(createTime.getTime()));
                            orderStmt.setTimestamp(21, new Timestamp(createTime.getTime()));
                            orderStmt.executeUpdate();

                            try (ResultSet rs = orderStmt.getGeneratedKeys()) {
                                if (rs.next()) {
                                    int orderId = rs.getInt(1);

                                    // 插入订单项
                                    for (OrderItem item : items) {
                                        try (PreparedStatement itemStmt = connection.prepareStatement(
                                                "INSERT INTO order_items (order_id, order_no, product_id, product_name, " +
                                                        "product_main_image, sku_id, sku_name, sku_attributes, price, quantity, total_price) " +
                                                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {

                                            itemStmt.setInt(1, orderId);
                                            itemStmt.setString(2, orderNo);
                                            itemStmt.setInt(3, item.productId);
                                            itemStmt.setString(4, item.productName);
                                            itemStmt.setString(5, item.productMainImage);
                                            itemStmt.setInt(6, item.skuId);
                                            itemStmt.setString(7, item.skuName);
                                            itemStmt.setString(8, item.skuAttributes);
                                            itemStmt.setDouble(9, item.price);
                                            itemStmt.setInt(10, item.quantity);
                                            itemStmt.setDouble(11, item.totalPrice);
                                            itemStmt.executeUpdate();
                                        }
                                    }

                                    // 插入支付记录
                                    if (paymentTime != null) {
                                        try (PreparedStatement paymentStmt = connection.prepareStatement(
                                                "INSERT INTO payments (order_id, order_no, transaction_no, " +
                                                        "payment_method, amount, status, pay_time) " +
                                                        "VALUES (?, ?, ?, ?, ?, ?, ?)")) {

                                            String transactionNo = "PAY" + faker.number().numberBetween(100000000, 999999999);
                                            paymentStmt.setInt(1, orderId);
                                            paymentStmt.setString(2, orderNo);
                                            paymentStmt.setString(3, transactionNo);
                                            paymentStmt.setInt(4, paymentMethod);
                                            paymentStmt.setDouble(5, payAmount);
                                            paymentStmt.setInt(6, 1); // 支付成功
                                            paymentStmt.setTimestamp(7, new Timestamp(paymentTime.getTime()));
                                            paymentStmt.executeUpdate();
                                        }
                                    }
                                }
                            }
                        }
                        connection.commit();
                    } catch (SQLException e) {
                        try {
                            connection.rollback();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                        e.printStackTrace();
                    } finally {
                        try {
                            connection.setAutoCommit(true);
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private void generateInventoryOperations(Map<Integer, List<ProductSku>> productSkusMap) {
        System.out.println("Generating inventory operations...");
        String sql = "INSERT INTO inventory_operations (sku_id, operation_type, quantity, source_no, operator, operation_time) " +
                "VALUES (?, ?, ?, ?, ?, ?)";

        // 1. 生成初始入库记录
        for (List<ProductSku> skus : productSkusMap.values()) {
            for (ProductSku sku : skus) {
                int initialStock = faker.number().numberBetween(100, 1000);
                String purchaseNo = "PUR" + faker.number().numberBetween(100000, 999999);

                if (skipDbInsertion) {
                    sqlBuilder.append(String.format(
                            "INSERT INTO inventory_operations (sku_id, operation_type, quantity, source_no, operator, operation_time) " +
                                    "VALUES (%d, 1, %d, '%s', 'system', NOW() - INTERVAL %d DAY);\n",
                            sku.id, initialStock, purchaseNo, faker.number().numberBetween(10, 30)
                    ));
                } else {
                    try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                        pstmt.setInt(1, sku.id);
                        pstmt.setInt(2, 1); // 1=入库
                        pstmt.setInt(3, initialStock);
                        pstmt.setString(4, purchaseNo);
                        pstmt.setString(5, "admin");
                        pstmt.setTimestamp(6, new Timestamp(faker.date().past(30, TimeUnit.DAYS).getTime()));
                        pstmt.executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        // 2. 生成其他库存操作（调拨、调整等）
        int otherOpsCount = productSkusMap.size() * 2;
        for (int i = 0; i < otherOpsCount; i++) {
            // 随机选择一个SKU
            int randomProductId = new ArrayList<>(productSkusMap.keySet())
                    .get(faker.random().nextInt(productSkusMap.size()));
            ProductSku sku = productSkusMap.get(randomProductId)
                    .get(faker.random().nextInt(productSkusMap.get(randomProductId).size()));

            int opType = faker.random().nextInt(3, 6); // 3=调拨, 4=调整, 5=预占, 6=释放
            int quantity = faker.random().nextInt(-50, 50);
            String sourceNo = "ADJ" + faker.number().numberBetween(10000, 99999);

            if (skipDbInsertion) {
                sqlBuilder.append(String.format(
                        "INSERT INTO inventory_operations (sku_id, operation_type, quantity, source_no, operator, operation_time) " +
                                "VALUES (%d, %d, %d, '%s', 'admin', NOW() - INTERVAL %d DAY);\n",
                        sku.id, opType, quantity, sourceNo, faker.number().numberBetween(1, 10)
                ));
            } else {
                try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                    pstmt.setInt(1, sku.id);
                    pstmt.setInt(2, opType);
                    pstmt.setInt(3, quantity);
                    pstmt.setString(4, sourceNo);
                    pstmt.setString(5, "admin");
                    pstmt.setTimestamp(6, new Timestamp(faker.date().past(10, TimeUnit.DAYS).getTime()));
                    pstmt.executeUpdate();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // === 辅助类 ===

    static class Product {
        int id;
        int categoryId;
        String name;
        String subtitle;
        String mainImage;
        double originPrice;
        int status;
        String brand;
        String unit;
        double weight;
    }

    static class ProductSku {
        int id;
        int productId;
        Product product;
        String name;
        String attributes;
        double price;
        int stock;
        int sales;
        String skuCode;
        String image;
    }

    static class Coupon {
        int id;
    }

    static class UserCoupon {
        int id;
        int userId;
        int couponId;
        String code;
        int status;
    }

    static class OrderItem {
        int productId;
        String productName;
        String productMainImage;
        int skuId;
        String skuName;
        String skuAttributes;
        double price;
        int quantity;
        double totalPrice;

        public OrderItem(int productId, String productName, String productMainImage,
                         int skuId, String skuName, String skuAttributes,
                         double price, int quantity, double totalPrice) {
            this.productId = productId;
            this.productName = productName;
            this.productMainImage = productMainImage;
            this.skuId = skuId;
            this.skuName = skuName;
            this.skuAttributes = skuAttributes;
            this.price = price;
            this.quantity = quantity;
            this.totalPrice = totalPrice;
        }
    }

    // === 辅助方法 ===

    private void connectToDatabase() throws SQLException {
        connection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
    }

    private void saveToFile() throws IOException {
        try (FileWriter writer = new FileWriter(outputFile)) {
            writer.write(sqlBuilder.toString());
        }
    }

    private void executeSqlStatements() throws SQLException {
        if (sqlBuilder.length() > 0) {
            try (Statement stmt = connection.createStatement()) {
                String[] sqlStatements = sqlBuilder.toString().split(";\n");
                for (String sql : sqlStatements) {
                    if (!sql.trim().isEmpty()) {
                        stmt.execute(sql);
                    }
                }
            }
        }
    }

    private void setNullableInt(PreparedStatement pstmt, int index, Integer value) throws SQLException {
        if (value != null) {
            pstmt.setInt(index, value);
        } else {
            pstmt.setNull(index, Types.INTEGER);
        }
    }

    private void setNullableDouble(PreparedStatement pstmt, int index, Double value) throws SQLException {
        if (value != null) {
            pstmt.setDouble(index, value);
        } else {
            pstmt.setNull(index, Types.DOUBLE);
        }
    }

    private void setNullableString(PreparedStatement pstmt, int index, String value) throws SQLException {
        if (value != null) {
            pstmt.setString(index, value);
        } else {
            pstmt.setNull(index, Types.VARCHAR);
        }
    }

    private void setNullableDate(PreparedStatement pstmt, int index, java.util.Date value) throws SQLException {
        if (value != null) {
            pstmt.setDate(index, new java.sql.Date(value.getTime()));
        } else {
            pstmt.setNull(index, Types.DATE);
        }
    }

    private void setNullableTimestamp(PreparedStatement pstmt, int index, java.util.Date value) throws SQLException {
        if (value != null) {
            pstmt.setTimestamp(index, new Timestamp(value.getTime()));
        } else {
            pstmt.setNull(index, Types.TIMESTAMP);
        }
    }
}